﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text.RegularExpressions;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ewal.Data;

namespace DefektaiApp
{
    public partial class frmSingleRecord : Form
    {
        BindingSource bindingSource = new BindingSource();
        long id = 0;

        // constructor to edit existing record
        public frmSingleRecord(long recordId)
        {
            InitializeComponent();
            id = recordId;
        }
        
        // constructor to create new record
        public frmSingleRecord()
        {
            InitializeComponent();                       
        }

        private void frmSingleRecord_Load(object sender, EventArgs e)
        {
            clearForm(true);

            // sukurti visiems comboboxams reikšmes 

            setComboBoxDataSource(cmbRasta_dfId,
                "SELECT id FROM Defektoskopai WHERE isActive = TRUE",
                "id", "id", false);

            setComboBoxDataSource(cmbIteikti_dfId,
                "SELECT id FROM Defektoskopai WHERE isActive = TRUE",
                "id", "id", true);

            setComboBoxDataSource(cmbIteikti_operId,
                "SELECT id, vardas FROM Operatoriai WHERE isActive = TRUE",
                "vardas", "id", true);

            if (id == 0)
            {
                this.bindingSource.DataSource = null;
            }
            else
            {
                string sqlStr = string.Format("SELECT * FROM Pranesimai WHERE id={0}", id);
                this.bindingSource.DataSource = DbHelper.FillDataTable(sqlStr);
                populateData();
            }
            mtxbVietosKodas.Focus();
            mtxbVietosKodas.Select();
        }
        
        private void populateData()
        {
            DataRowView row = (DataRowView)bindingSource.Current;

            txbId.Text = row["id"].ToString();

            // gali būti atspausdintas, bet su nustatyta nesąmoninga spausdinimo data
            // todėl dėl viso pikto tikrinama, ar data suprantama
            dtpAtspausdinta_data.Checked = (bool)row["atspausdinta"];
            try
            {
                dtpAtspausdinta_data.Value = (DateTime)row["spausdinta_data"];
            }
            catch
            {
                dtpAtspausdinta_data.Value = dtpAtspausdinta_data.MinDate;
                dtpAtspausdinta_data.Text = string.Empty;
            }

            mtxbVietosKodas.Text = makeVietosKodasString(row);
            txbD0.Text = row["d0"].ToString();
            txbD70p.Text = row["d70p"].ToString();
            txbD55ad.Text = row["d55ad"].ToString();
            txbD55an.Text = row["d55an"].ToString();
            txbD42p.Text = row["d42p"].ToString();
            txbD42a.Text = row["d42a"].ToString();
            txbD70a.Text = row["d70a"].ToString();
            txbD55an.Text = row["d55an"].ToString();
            txbPastaba.Text = row["pastaba"].ToString();            
            cmbRasta_dfId.SelectedValue = row["rasta_dfId"];
            setDateTimePickerValue(dtpRasta_data, (DateTime)row["rasta_data"]);
            txbDefFailoNr.Text = row["defFailoNr"].ToString();            
            cmbIteikti_dfId.SelectedValue = row["iteikti_dfId"];
            setDateTimePickerValue(dtpIteikti_data, row["iteikti_data"]);            
            cmbIteikti_operId.SelectedValue = row["iteikti_operId"];
            chbJournal.Checked = (bool)row["zurnale"];
            txbRezultatas.Text = row["rezultatas"].ToString();
        }

        private string makeVietosKodasString(DataRowView rw)
        {
            return rw["k11"].ToString() + rw["k12"].ToString() + "." +
                 rw["k21"].ToString() + rw["k22"].ToString() + rw["k23"].ToString() + rw["k24"].ToString() + "." +
                 rw["k31"].ToString() + rw["k32"].ToString() + "." +
                 rw["k41"].ToString() + rw["k42"].ToString() + "." +
                 rw["k51"].ToString();
        }

        private void btnIseiti_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnIssaugotiPlus_Click(object sender, EventArgs e)
        {
            // įrašomas įrašas
            saveRecord();

            // formos pilnai neišvalyti - dalis duomenų gali būti panaudota kitam įrašui
            clearForm(false);
        }

        private void btnIssaugoti_Click(object sender, EventArgs e)
        {
            // įrašomas įrašas
            // jeigu įrašyti nepavyko - nutraukti procedūrą, kol neištrynė visų į formą įvestų duomenų.
           if(saveRecord() == 0) return;
            
            // nustatoma naujam įrašui
            id = 0;

            // forma išvaloma pilnai
            clearForm(true);
        }

        private void clearForm(bool fullClear)
        {
            this.bindingSource.DataSource = null;

            txbId.Text = string.Empty;         
            dtpAtspausdinta_data.Text = string.Empty;
            dtpAtspausdinta_data.Checked = false;            
            
            // šitie tikrai keičiasi            
            mtxbVietosKodas.ResetText();
            txbD0.Text = string.Empty;
            txbD70p.Text = string.Empty;
            txbD55ad.Text = string.Empty;
            txbD55an.Text = string.Empty;
            txbD42p.Text = string.Empty;
            txbD42a.Text = string.Empty;
            txbD70a.Text = string.Empty;
            txbD55pd.Text = string.Empty;
            txbPastaba.Text = string.Empty;
            txbRezultatas.Text = string.Empty;
            chbJournal.Checked = true;

            if (fullClear) // šitie gali nesikeisti
            {
                cmbRasta_dfId.SelectedIndex = -1;
                dtpRasta_data.Value = dtpRasta_data.MinDate;
                dtpRasta_data.Text = string.Empty;
                txbDefFailoNr.Text = string.Empty;
                cmbIteikti_dfId.SelectedIndex = -1;
                dtpIteikti_data.Text = string.Empty;
                dtpIteikti_data.Checked = false;
                cmbIteikti_operId.SelectedIndex = -1;
            }
            mtxbVietosKodas.Focus();
            mtxbVietosKodas.Select();
            
        }

        private void setComboBoxDataSource(
                    ComboBox combo,
                    string sqlSelectStatement,
                    string displayMemberName,
                    string valueMemberName,
                    bool emptyrow)
        {
            DataTable dTable = DbHelper.FillDataTable(sqlSelectStatement);
            if (emptyrow)
            {
                DataRow emptyRow = dTable.NewRow();
                dTable.Rows.InsertAt(emptyRow, 0);
            }
            combo.DataSource = dTable;
            combo.DisplayMember = displayMemberName;
            combo.ValueMember = valueMemberName;
        }

        private void setDateTimePickerValue(
            DateTimePicker dtp,
            Object valueToSet)
        {
            try
            {
                dtp.Value = (DateTime)valueToSet;
                dtp.Checked = true;
            }
            catch
            {
                dtp.Value = dtp.MinDate;
                //dtp.Text = string.Empty;    //?
                dtp.Checked = false;
            }
        }

        private int saveRecord()
        {
            // Start collecting data
            Dictionary<string, string> kvps = new Dictionary<string, string>();
            kvps.Add("atspausdinta", dtpAtspausdinta_data.Checked.ToString());
            kvps.Add("spausdinta_data", dtpValue(dtpAtspausdinta_data));

            // padaryti kažką su tuo masked
            kvps.Add("k11", maskedTextChar(0));
            kvps.Add("k12", maskedTextChar(1));
            kvps.Add("k21", maskedTextChar(3));
            kvps.Add("k22", maskedTextChar(4));
            kvps.Add("k23", maskedTextChar(5));
            kvps.Add("k24", maskedTextChar(6));
            kvps.Add("k31", maskedTextChar(8));
            kvps.Add("k32", maskedTextChar(9));
            kvps.Add("k41", maskedTextChar(11));
            kvps.Add("k42", maskedTextChar(12));
            kvps.Add("k51", maskedTextChar(14));

            kvps.Add("d0", txbValue(txbD0));
            kvps.Add("d70p", txbValue(txbD70p));
            kvps.Add("d55ad", txbValue(txbD55ad));
            kvps.Add("d55an", txbValue(txbD55an));
            kvps.Add("d42p", txbValue(txbD42p));
            kvps.Add("d42a", txbValue(txbD42a));
            kvps.Add("d70a", txbValue(txbD70a));
            kvps.Add("d55pd", txbValue(txbD55pd));
            kvps.Add("pastaba", txbValue(txbPastaba));

            kvps.Add("rasta_dfId", cmbNumValue(cmbRasta_dfId));
            kvps.Add("defFailoNr", txbValue(txbDefFailoNr));
            kvps.Add("rasta_data", dtpValue(dtpRasta_data));

            kvps.Add("iteikti_dfId", cmbNumValue(cmbIteikti_dfId));
            kvps.Add("iteikti_data", dtpValue(dtpIteikti_data));
            kvps.Add("iteikti_operId", cmbNumValue(cmbIteikti_operId));
            kvps.Add("zurnale", chbValue(chbJournal));
            kvps.Add("rezultatas", txbValue(txbRezultatas));
            // End collecting data

            // format sql statement
            string sqlSt;
            if (id == 0)
            {
                sqlSt = sqlInsert("Pranesimai", kvps);
            }
            else
            {
                sqlSt = sqlUpdate("Pranesimai", kvps, "id=" + id.ToString());
            }

            // execute sql
            try
            {
                return DbHelper.ExecuteNonQuery(sqlSt);
            }
            catch
            {
                ewal.Msg.Msg.ErrorMsg("Nepavyksta atlikti sql užklausos ExecuteNonQuery:" + Environment.NewLine + sqlSt);
                return 0;
            }
            //frmAllPlaces.dgvAllPlaces.Refresh();
        }
        
        private string txbValue (TextBox txb)
        {
            string str;
            if (string.IsNullOrWhiteSpace(txb.Text))
            {
                str = string.Empty;
            }
            else
            {
                str = sanit(txb.Text);
            }
            return string.Format("'{0}'", str);
        }

        private string maskedTextChar(int position)
        {
            try
            {
                return string.Format("'{0}'", Convert.ToByte(mtxbVietosKodas.Text[position].ToString()));
            }
            catch
            {
                return "NULL";
            }
        }

        private string cmbNumValue(ComboBox cmb)
        {
            if (cmb.SelectedIndex > -1 && cmb.SelectedValue != null && !DBNull.Value.Equals(cmb.SelectedValue))
            {
                return sanit(cmb.SelectedValue.ToString());
            }
            else
            {
                return "NULL";
            }
        }

        private string cmbStringValue(ComboBox cmb)
        {
            if (cmb.SelectedIndex > -1 && cmb.SelectedValue != null && !DBNull.Value.Equals(cmb.SelectedValue))
            {
                return string.Format("'{0}'", sanit(cmb.SelectedValue.ToString()));
            }
            else
            {
                return "NULL";
            }
        }

        private string dtpValue(DateTimePicker dtp)
        {
            if (dtp.Checked)
            {
                return DbHelper.FormatDateValue(dtp.Value);
            }
            else
            {
                return "NULL";
            }
        }

        private string chbValue(CheckBox chb)
        {
            if (chb.Checked)
            {
                return "TRUE";
            }
            else
            {
                return "FALSE";
            }
        }

        private string sqlUpdate(string tableName, Dictionary<string, string> colvalpairs, string where)
        {
            // UPDATE Pranesimai SET column=value, column=value, colum=value, ... WHERE id = id;
            
            StringBuilder sbColVal = new StringBuilder();
            foreach (KeyValuePair<string, string> colval in colvalpairs)
            {
                sbColVal.Append(string.Format("{0}={1}, ", colval.Key, colval.Value));
            }
            sbColVal.Remove(sbColVal.Length - 2, 2);

            return string.Format("UPDATE {0} SET {1} WHERE {2};", tableName, sbColVal.ToString(), where);
        }

        private string sqlInsert(string tableName, Dictionary<string, string> colvalpairs)
        {
            // INSERT INTO Pranesimai (column, column, column, ...) VALUES (value, value, value, ...);
            
            StringBuilder sbColumns = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            foreach (KeyValuePair<string, string> colval in colvalpairs)
            {
                sbColumns.Append(colval.Key);
                sbColumns.Append(", ");

                sbValues.Append(colval.Value);
                sbValues.Append(", ");
            }

            sbColumns.Remove(sbColumns.Length - 2, 2);
            sbValues.Remove(sbValues.Length - 2, 2);
            
            return string.Format("INSERT INTO {0} ({1}) VALUES ({2});", tableName, sbColumns.ToString(), sbValues.ToString());            
        }

        private string sanit(string strToSanitize)
        {
            Regex forb = new Regex("['\"]*");
            return forb.Replace(strToSanitize, string.Empty).Trim();
        }

        private void txbD0_Enter(object sender, EventArgs e)
        {
            txbD0.SelectAll();
        }

        private void txbD70p_Enter(object sender, EventArgs e)
        {
            txbD70p.SelectAll();
        }

        private void txbD55ad_Enter(object sender, EventArgs e)
        {
            txbD55ad.SelectAll();
        }

        private void txbD55an_Enter(object sender, EventArgs e)
        {
            txbD55an.SelectAll();
        }

        private void txbD42p_Enter(object sender, EventArgs e)
        {
            txbD42p.SelectAll();
        }

        private void txbD42a_Enter(object sender, EventArgs e)
        {
            txbD42a.SelectAll();
        }

        private void txbD70a_Enter(object sender, EventArgs e)
        {
            txbD70a.SelectAll();
        }

        private void txbD55pd_Enter(object sender, EventArgs e)
        {
            txbD55pd.SelectAll();
        }

        private void txbPastaba_Enter(object sender, EventArgs e)
        {
            txbPastaba.SelectAll();
        }
    
    }
}
